﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
using System.Text;

using Model;
namespace DAL
{
    #region class SQLDALarea
    /// <summary>
    /// it is suitable to the table with only one primary key,and can not deal with the aotu-increment key.
    /// Note:the first field in the table must be primary and autoincrement and int ,if exists 
    /// class:DAL area 
    /// Author:wxc
    /// DateTime:2021/5/29 14:06:48
    /// </summary>	
    public class DALarea
    {
        #region SQL CONST		
        private const string INSERT_SQL = "INSERT INTO [area] ([code],[name],[citycode]) Values (@code,@name,@citycode)";
        private const string UPDATE_SQL = "UPDATE [area] SET code=@code,name=@name,citycode=@citycode WHERE [id]=@id";
        private const string DEL_SQL = "DELETE FROM [area] WHERE [id]=@id";
        private const string SELECT_ALL_SQL = "SELECT [id],[code],[name],[citycode] FROM [area] order by id desc";
        private const string SELECT_ONE_SQL = "SELECT [id],[code],[name],[citycode] FROM [area] WHERE [id]=@id";
        private const string SELECT_WHERE_SQL = "SELECT [id],[code],[name],[citycode] FROM [area] {$where} order by  id desc";
        #endregion
        #region Paras const
        private const string PARA_id = "@id";
        private const string PARA_code = "@code";
        private const string PARA_name = "@name";
        private const string PARA_citycode = "@citycode";
        #endregion
        #region Add Method
        /// <summary>
        /// Insert an item
        /// </summary>
        /// <param name="area"></param>
        /// <returns>count of rows affected</returns>
        public int Addarea(areaEntity area)
        {
            int backvalue = 0;
            //get paras
            SqlParameter[] areaParms = GetareaParametersInsert();
            //set values to paras
            areaParms[0].Value = area.code;
            areaParms[1].Value = area.name;
            areaParms[2].Value = area.citycode;
            //insert			
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, INSERT_SQL, areaParms);
            }
            return backvalue;
        }
        #endregion
        #region Mod Method
        /// <summary>
        /// Update an item
        /// </summary>
        /// <param name="area"></param>
        /// <returns>count of rows affected</returns>
        public int Modarea(areaEntity area)
        {
            int backvalue = 0;
            //get paras
            SqlParameter[] areaParms = GetareaParameters();
            //set values to paras
            areaParms[0].Value = area.id;
            areaParms[1].Value = area.code;
            areaParms[2].Value = area.name;
            areaParms[3].Value = area.citycode;
            //update			
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, UPDATE_SQL, areaParms);
            }
            return backvalue;
        }
        #endregion
        #region Del Method
        /// <summary>
        /// Del an item by primary key
        /// </summary>
        /// <param name="id"></param> 
        /// <returns>count of rows affected</returns>
        public int Delarea(int id)
        {
            int backvalue = 0;
            //primarykeys
            //get paras
            SqlParameter[] areaParms = GetareaPrimaryKeyParameters();
            //set values to paras
            areaParms[0].Value = id;
            //delete
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, DEL_SQL, areaParms);
            }
            return backvalue;
        }
        #endregion
        #region Select Method
        /// <summary>
        /// Select an item by Primarykey
        /// </summary>
        /// <param name="id"></param>
        /// <returns>an item</returns>
        public areaEntity Getarea(int id)
        {
            areaEntity area = new areaEntity();
            //primarykeys
            //get paras
            SqlParameter[] areaParms = GetareaPrimaryKeyParameters();
            //set values to paras
            areaParms[0].Value = id;
            //select
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ONE_SQL, areaParms))
            {
                if (reader.Read())
                {
                    if (!reader.IsDBNull(0)) area.id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) area.code = reader.GetString(1);
                    if (!reader.IsDBNull(2)) area.name = reader.GetString(2);
                    if (!reader.IsDBNull(3)) area.citycode = reader.GetString(3);
                }
                else
                {
                    area = null;
                }
            }
            return area;

        }
        #endregion
        #region Select_All Method
        /// <summary>
        /// Select all items
        /// </summary>
        /// <returns>items</returns>
        public IList<areaEntity> Getareas()
        {
            IList<areaEntity> areas = new List<areaEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ALL_SQL, null))
            {
                while (reader.Read())
                {
                    areaEntity area = new areaEntity();
                    if (!reader.IsDBNull(0)) area.id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) area.code = reader.GetString(1);
                    if (!reader.IsDBNull(2)) area.name = reader.GetString(2);
                    if (!reader.IsDBNull(3)) area.citycode = reader.GetString(3);
                    areas.Add(area);
                }
            }
            return areas;
        }
        #endregion
        #region private common select by conditions
        /// <summary>
        /// private common select by conditions
        /// </summary>
        /// <returns>IList<areaEntity></returns>
        private IList<areaEntity> GetareasbyQueryString(string SQLstr)
        {
            IList<areaEntity> areas = new List<areaEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SQLstr))
            {
                while (reader.Read())
                {
                    areaEntity area = new areaEntity();
                    if (!reader.IsDBNull(0)) area.id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) area.code = reader.GetString(1);
                    if (!reader.IsDBNull(2)) area.name = reader.GetString(2);
                    if (!reader.IsDBNull(3)) area.citycode = reader.GetString(3);
                    areas.Add(area);
                }
            }
            return areas;
        }
        #endregion
        #region public common select by conditions ,we can use it by changing the GetareasbyCondition(datatype condition) and sql
        /// <summary>
        /// Select items by Columns
        /// </summary>
        /// <returns>IList<areaEntity></returns>
        public IList<areaEntity> GetareasbyCondition(string sql)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SELECT_WHERE_SQL);
            sb.Replace("{$where}", "where " + sql);
            return GetareasbyQueryString(sb.ToString());
        }
        #endregion
        #region Select Method Top n
        /// <summary>
        /// Select items by Columns
        /// </summary>
        /// <returns>IList<areaEntity></returns>
        public IList<areaEntity> GetareasTopN(int n)
        {
            IList<areaEntity> areas = new List<areaEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ALL_SQL, null))
            {
                int i = 0;
                while (reader.Read() && i < n)
                {
                    areaEntity area = new areaEntity();
                    if (!reader.IsDBNull(0)) area.id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) area.code = reader.GetString(1);
                    if (!reader.IsDBNull(2)) area.name = reader.GetString(2);
                    if (!reader.IsDBNull(3)) area.citycode = reader.GetString(3);
                    areas.Add(area);
                    i++;
                }
            }
            return areas;
        }
        #endregion
        #region paras configuration
        /// <summary>
        /// Internal function to get cached parameters:all paras --- suitable to insert ,especially autoincrement
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetareaParametersInsert()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_ALL_area_Insert");

            if (parms == null)
            {
                parms = new SqlParameter[] {
            new SqlParameter(PARA_code,SqlDbType.VarChar) ,
            new SqlParameter(PARA_name,SqlDbType.VarChar) ,
            new SqlParameter(PARA_citycode,SqlDbType.VarChar)           };
                SQLHelper.CacheParameters("SQL_ALL_area_Insert", parms);
            }
            return parms;
        }
        /// <summary>
        /// Internal function to get cached parameters:all paras --- suitable to update
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetareaParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_ALL_area_Update");

            if (parms == null)
            {
                parms = new SqlParameter[] {
                    new SqlParameter(PARA_id,SqlDbType.Int) ,
                    new SqlParameter(PARA_code,SqlDbType.VarChar) ,
                    new SqlParameter(PARA_name,SqlDbType.VarChar) ,
                    new SqlParameter(PARA_citycode,SqlDbType.VarChar)                   };
                SQLHelper.CacheParameters("SQL_ALL_area_Update", parms);
            }
            return parms;
        }
        /// <summary>
        /// Internal function to get cached parameters:primary key paras
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetareaPrimaryKeyParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_PrimaryKey_area");

            if (parms == null)
            {
                parms = new SqlParameter[] {
                    new SqlParameter(PARA_id,SqlDbType.Int) ,
                    };
                SQLHelper.CacheParameters("SQL_PrimaryKey_area", parms);
            }
            return parms;
        }
        #endregion
    }
    #endregion
}
